﻿using System;
using System.Data;
using System.Configuration;
using System.Collections.Generic;
using System.Data.SQLite;
using Common;

namespace BusDiary.Access
{
    public partial class DiaryStatus
    {
        static object addLock = new object();

        string insertSQL = "insert into DiaryStatus([Date],[Weather],[Emotion],[Health],[Wakeup],[Sleep])  values(@Date,@Weather,@Emotion,@Health,@Wakeup,@Sleep)";
        /// <summary>
        /// 添加一条新的记录
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Add(BusDiary.Entity.DiaryStatus model)
        {
            SQLiteParameter[] parameters = new SQLiteParameter[]{
                new SQLiteParameter("@Date", model.Date),
                new SQLiteParameter("@Weather", model.Weather),
                new SQLiteParameter("@Emotion", model.Emotion),
                new SQLiteParameter("@Health", model.Health),
                new SQLiteParameter("@Wakeup", model.Wakeup),
                new SQLiteParameter("@Sleep", model.Sleep),};
            lock (addLock)
            {
                int row = SQLiteHelper.ExecuteNonQuery(insertSQL, parameters);

                DataSet ds = SQLiteHelper.ExecuteDataset("select max(id) from DiaryStatus");
                int id = (int)(Int64)ds.Tables[0].Rows[0][0];
                model.ID = id;
            }

            return model.ID;
        }

        string updateSQL = "update DiaryStatus Set [Date]=@Date,[Weather]=@Weather,[Emotion]=@Emotion,[Health]=@Health,[Wakeup]=@Wakeup,[Sleep]=@Sleep where [ID]=@ID";        /// <summary>
        /// 更新一个对象
        /// </summary>
        /// <param name="model"></param>
        public void Update(BusDiary.Entity.DiaryStatus model)
        {
            SQLiteParameter[] parameters = new SQLiteParameter[]{
                new SQLiteParameter("@ID", model.ID),
                new SQLiteParameter("@Date", model.Date),
                new SQLiteParameter("@Weather", model.Weather),
                new SQLiteParameter("@Emotion", model.Emotion),
                new SQLiteParameter("@Health", model.Health),
                new SQLiteParameter("@Wakeup", model.Wakeup),
                new SQLiteParameter("@Sleep", model.Sleep),};
            SQLiteHelper.ExecuteNonQuery(updateSQL, parameters);
        }

        string deleteSQL = "delete from DiaryStatus where [ID]=@ID";

        /// <summary>
        /// 删除一个对象
        /// </summary>
        /// <param name="model"></param>
        public void Delete(BusDiary.Entity.DiaryStatus model)
        {
            Delete(model.ID);
        }

        /// <summary>
        /// 通过关键字段ID删除一条记录
        /// </summary>
        /// <param name="ID"></param>
        public void Delete(int ID)
        {
            SQLiteParameter[] parameters = new SQLiteParameter[]{
                new SQLiteParameter("@ID", ID)};

            SQLiteHelper.ExecuteNonQuery(deleteSQL, parameters);

        }

        string selectSQL = "select * from DiaryStatus";

        /// <summary>
        /// 根据关键字段ID获得一条数据记录
        /// 如果不存在记录则返回NULL
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public BusDiary.Entity.DiaryStatus GetByID(int ID)
        {
            SQLiteParameter[] parameters = new SQLiteParameter[]{
                new SQLiteParameter("@ID", ID)};

            string sql = selectSQL + " where ID=@ID";

            IDataReader reader = SQLiteHelper.ExecuteReader(sql, parameters);

            return GetSingle(reader);
        }

        /// <summary>
        /// 从返回的记录里获得第一条数据
        /// 如果没有数据，则返回NULL
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        private static BusDiary.Entity.DiaryStatus GetSingle(IDataReader reader)
        {
            BusDiary.Entity.DiaryStatus ret = null;

            try
            {
                if (reader.Read())
                {
                    ret = GetModel(reader);
                }
            }
            finally
            {
                reader.Close();
            }

            return ret;
        }

        /// <summary>
        /// 获得数据库表里所有数据
        /// </summary>
        /// <returns></returns>
        public List<BusDiary.Entity.DiaryStatus> GetAll()
        {
            IDataReader reader = SQLiteHelper.ExecuteReader(selectSQL);

            return GetMultiEntity(reader);        
        }

        /// <summary>
        /// 从DataReader里获得多条记录
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        private static List<BusDiary.Entity.DiaryStatus> GetMultiEntity(IDataReader reader)
        {
            List<BusDiary.Entity.DiaryStatus> ret = new List<BusDiary.Entity.DiaryStatus>();

            try
            {
                while (reader.Read())
                {
                    ret.Add(GetModel(reader));
                }
            }
            finally
            {
                reader.Close();
            }

            return ret;
        }

        private static BusDiary.Entity.DiaryStatus GetModel(IDataReader reader)
        {
            BusDiary.Entity.DiaryStatus ret = new BusDiary.Entity.DiaryStatus();

            ret.ID = (int)(Int64)reader["ID"];
            ret.Date = (DateTime)reader["Date"];
            ret.Weather = (string)reader["Weather"];
            ret.Emotion = (string)reader["Emotion"];
            ret.Health = (string)reader["Health"];
            ret.Wakeup = (DateTime)reader["Wakeup"];
            ret.Sleep = (DateTime)reader["Sleep"];

            return ret;
        }
        static DiaryStatus instance = null;

        public static DiaryStatus Instance
        {
            get
            {
                if (instance == null)
                    instance = new DiaryStatus();
                return DiaryStatus.instance;
            }
        }

    }
}

